﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExtendedDal;
using SD.LLBLGen.Pro.LinqSupportClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using WHMS.HelperClasses;
using System.Data;
using WHMS.EntityClasses;
using WHMS.Linq;
using WHMS.TypedListClasses;
using StringCryptography;
using WHMS.DatabaseSpecific;
using WHMS;
using WHMS.TypedViewClasses;
using System.ComponentModel;
using WMS.Reports;

namespace WMS.HelperClasses
{
    public static class Business
    {
        public static bool AuthenticateUser(string userName, string pswd)
        {
            userName = userName.Trim();
            pswd = pswd.Trim();

            if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(pswd))
                return false;

            var fields = new EntityFields2(1);
            fields.Add(UserFields.Id);

            var prefetch = new PrefetchPath2(EntityType.UserEntity);
            prefetch.Add(UserEntity.PrefetchPathUserRoles).SubPath.Add(UserRoleEntity.PrefetchPathRole);
            var users = new EntityCollection<UserEntity>();
            DALHelper.FetchCollection(users, new RelationPredicateBucket(UserFields.UserName == userName), prefetch);

            EntityCollection<RoleEntity> roles = new EntityCollection<RoleEntity>();

            if (users.Count <= 0)
                return false;


            foreach (UserEntity user in users)
            {
                byte[] IV = Convert.FromBase64String(user.Salt);
                Decryptor dec = new Decryptor(EncryptionAlgorithm.DES, IV);
                if (pswd == dec.Decrypt(user.Password, "25012011"))
                {
                    UserInfo.UserId = user.Id;

                    foreach (UserRoleEntity userrole in user.UserRoles)
                    {
                        roles.Add(userrole.Role);
                    }
                    UserInfo.Roles = roles;
                    return true;
                }
            }

            return false;
        }
        public static int GetCurrentUserId()
        {
            return UserInfo.UserId;
        }
        public static bool SavePassword(int userId, string password)
        {
            var encryptor = new Encryptor(EncryptionAlgorithm.DES, "25012011");
            var encryptedPassword = encryptor.Encrypt(password.Trim());
            var salt = Convert.ToBase64String(encryptor.IV);

            return DALHelper.SaveEntity(new UserEntity(userId) { Password = encryptedPassword, Salt = salt, IsNew = false });
        }
        public static EntityCollection<T> GetGenericCollection<T>(IEntityCollection2 collection) where T : EntityBase2
        {
            var generic = new EntityCollection<T>();
            foreach (T entity in collection)
            {
                generic.Add(entity);
            }
            return generic;

        }
        public static void GetItemsPlannedForProject(int projectId, IEntityCollection2 items)
        {
            items.Clear();

            var filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(new FieldCompareSetPredicate(ItemFields.Id, null, ProjectItemPlanFields.ItemId, null, SetOperator.In, new PredicateExpression(ProjectItemPlanFields.ProjectId == projectId)));

            DALHelper.FetchCollection(items, filter);
        }

        public static void GetSupplyOrderItemsPlannedForProject(int projectId, ItemViewTypedView items)
        {
            GetItemsPlannedForProject(projectId, items, new RelationPredicateBucket(ItemViewFields.TypeId != 3));
        }

        public static void GetRawItemsPlannedForProject(int projectId, ItemViewTypedView items)
        {
            GetItemsPlannedForProject(projectId, items, new RelationPredicateBucket(ItemViewFields.TypeId == 3));
        }

        public static void GetItemsPlannedForProject(int projectId, ItemViewTypedView items)
        {
            GetItemsPlannedForProject(projectId, items, null);
            //items.Clear();

            //var filter = new RelationPredicateBucket();
            //filter.PredicateExpression.Add(new FieldCompareSetPredicate(ItemViewFields.Id, null, ProjectItemPlanFields.ItemId, null, SetOperator.In, new PredicateExpression(ProjectItemPlanFields.ProjectId == projectId)));

            //DALHelper.GetTypedView(items, filter, new SortExpression(ItemViewFields.NameCodeUnit | SortOperator.Ascending));
        }

        public static void GetItemsPlannedForProject(int projectId, ItemViewTypedView items, RelationPredicateBucket filter)
        {
            items.Clear();

            if (filter == null)
            {
                filter = new RelationPredicateBucket();
            }
            filter.PredicateExpression.Add(new FieldCompareSetPredicate(ItemViewFields.Id, null, ProjectItemPlanFields.ItemId, null, SetOperator.In, new PredicateExpression(ProjectItemPlanFields.ProjectId == projectId)));

            DALHelper.GetTypedView(items, filter, new SortExpression(ItemViewFields.NameCodeUnit | SortOperator.Ascending));
        }
        public static void GetItemsPlannedForProject(ItemViewTypedView items, RelationPredicateBucket filter)
        {
            items.Clear();

            if (filter == null)
            {
                filter = new RelationPredicateBucket();
            }

            DALHelper.GetTypedView(items, filter, new SortExpression(ItemViewFields.NameCodeUnit | SortOperator.Ascending));
        }

        public static DataTable GetItemsView(RelationPredicateBucket filter)
        {
            ItemViewTypedView items = new ItemViewTypedView();
            DALHelper.GetTypedView(items, filter, new SortExpression(ItemViewFields.NameCodeUnit | SortOperator.Ascending));
            return items;
        }

        public static DataTable GetItemDetails(string itemCode)
        {
            //var fields = new ResultsetFields(3);
            //fields.DefineField(ItemFields.Id, 0);
            //fields.DefineField(ItemFields.Name, 1);
            //fields.DefineField(UnitFields.Name, 2, "Unit");

            //var filter = new RelationPredicateBucket(ItemFields.Code == itemCode);
            //filter.Relations.Add(ItemEntity.Relations.UnitEntityUsingUnitId);

            //return DALHelper.GetDynamicList(fields, filter, null);
            return GetItemDetails(itemCode, 0);
        }
        public static DataTable GetItemDetails(string itemCode, int itemType)
        {
            var fields = new ResultsetFields(5);
            fields.DefineField(ItemFields.Id, 0);
            fields.DefineField(ItemFields.Name, 1);
            fields.DefineField(UnitFields.Name, 2, "Unit");
            fields.DefineField(ItemFields.BarCode, 3);
            fields.DefineField(ItemFields.OriginalBarCode, 4);

            var filter = new RelationPredicateBucket(ItemFields.Code == itemCode);
            filter.Relations.Add(ItemEntity.Relations.UnitEntityUsingUnitId);

            if (itemType > 0)
            {
                filter.PredicateExpression.Add(ItemFields.TypeId == itemType);
            }

            return DALHelper.GetDynamicList(fields, filter, null);
        }
        public static DataTable GetStoreDetails(string itemCode)
        {
            var fields = new ResultsetFields(4);
            fields.DefineField(StoreFields.Id, 0);
            fields.DefineField(StoreFields.Code, 1);
            fields.DefineField(StoreFields.Name, 2);
            fields.DefineField(StoreFields.Type, 3);

            RelationPredicateBucket filter = null;
            if (!String.IsNullOrEmpty(itemCode))
            {
                filter = new RelationPredicateBucket(StoreFields.Code == itemCode);
            }

            return DALHelper.GetDynamicList(fields, filter, null);

        }

        public static DataTable GetSupplierDetails(string supplierCode)
        {
            var fields = new ResultsetFields(3);
            fields.DefineField(SupplierFields.Id, 0);
            fields.DefineField(SupplierFields.Code, 1);
            fields.DefineField(SupplierFields.Name, 2);

            RelationPredicateBucket filter = null;
            if (!String.IsNullOrEmpty(supplierCode))
            {
                filter = new RelationPredicateBucket(SupplierFields.Code == supplierCode);
            }

            return DALHelper.GetDynamicList(fields, filter, null);

        }

        public static DataTable GetContractorDetails(string contractorCode)
        {
            var fields = new ResultsetFields(3);
            fields.DefineField(ContractorFields.Id, 0);
            fields.DefineField(ContractorFields.Code, 1);
            fields.DefineField(ContractorFields.Name, 2);

            RelationPredicateBucket filter = null;
            if (!String.IsNullOrEmpty(contractorCode))
            {
                filter = new RelationPredicateBucket(ContractorFields.Code == contractorCode);
            }

            return DALHelper.GetDynamicList(fields, filter, null);

        }
        /// <summary>
        /// Retrieve info of store based on Id of store 
        /// </summary>
        /// <param name="storeId">Id of store</param>
        /// <returns>Datatable consist of Id,code,name and type of store</returns>
        public static DataTable GetStoreDetails(int storeId)
        {
            var fields = new ResultsetFields(4);
            fields.DefineField(StoreFields.Id, 0);
            fields.DefineField(StoreFields.Code, 1);
            fields.DefineField(StoreFields.Name, 2);
            fields.DefineField(StoreFields.Type, 3);

            RelationPredicateBucket filter = null;

            filter = new RelationPredicateBucket(StoreFields.Id == storeId);


            return DALHelper.GetDynamicList(fields, filter, null);

        }

        public static DataTable GetShelfDetails(string itemCode)
        {
            var fields = new ResultsetFields(5);
            fields.DefineField(ShelfFields.Id, 0);
            fields.DefineField(ShelfFields.Code, 1);
            fields.DefineField(ShelfFields.Type, 2);
            fields.DefineField(ShelfFields.BarCode, 3);
            fields.DefineField(ShelfFields.OriginalBarCode, 4);
            var filter = new RelationPredicateBucket(ShelfFields.Code == itemCode);

            return DALHelper.GetDynamicList(fields, filter, null);

        }
        public static DataTable GetItemDetails(int itemId)
        {
            var fields = new ResultsetFields(4);
            fields.DefineField(ItemFields.Code, 0);
            fields.DefineField(ItemFields.Name, 1);
            fields.DefineField(UnitFields.Name, 2, "Unit");
            fields.DefineField(ItemFields.Weight, 3);

            var filter = new RelationPredicateBucket(ItemFields.Id == itemId);
            filter.Relations.Add(ItemEntity.Relations.UnitEntityUsingUnitId);

            return DALHelper.GetDynamicList(fields, filter, null);
        }

        public static void GetPrimaryStores(IEntityCollection2 primaryStores)
        {
            primaryStores.Clear();
            DALHelper.FetchCollection(primaryStores, new RelationPredicateBucket(StoreFields.IsPrimary == true));
        }
        public static DataTable GetAccountants(EntityField2 valueMember, EntityField2 displayMember)
        {
            var filter = new RelationPredicateBucket();
            var roleRelation = new RelationCollection() { UserRoleEntity.Relations.RoleEntityUsingRoleId };
            filter.PredicateExpression.Add(new FieldCompareSetPredicate(UserFields.Id, null, UserRoleFields.UserId, null, SetOperator.In, new PredicateExpression(RoleFields.CanAddPrice == true), roleRelation));

            return DALHelper.GetDynamicList(valueMember, displayMember, filter);
        }

        public static DataTable GetRunningProjects()
        {
            var filter = new RelationPredicateBucket(ProjectFields.Status == "جارى");
            filter.PredicateExpression.Add(ProjectFields.IsGeneral == false);//new FieldLikePredicate(ProjectFields.Name, null, "%مشروع عام%", true));
            return DALHelper.GetDynamicList(ProjectFields.Id, ProjectFields.Name, filter);
        }
        public static DataTable GetGeneralProjects()
        {
            return DALHelper.GetDynamicList(ProjectFields.Id, ProjectFields.Name, new RelationPredicateBucket(ProjectFields.IsGeneral == true));
        }
        public static DataTable GetGeneralProjects(int execeptId)
        {
            var filster = new RelationPredicateBucket(ProjectFields.IsGeneral == true);
            filster.PredicateExpression.Add(ProjectFields.Id != execeptId);
            return DALHelper.GetDynamicList(ProjectFields.Id, ProjectFields.Name, filster);
        }
        public static DataTable GetRunningAndGeneralProjects()
        {
            var filter = new RelationPredicateBucket(ProjectFields.Status == "جارى");
            filter.PredicateExpression.AddWithOr(ProjectFields.IsGeneral == true);//new FieldLikePredicate(ProjectFields.Name, null, "%مشروع عام%"));
            return DALHelper.GetDynamicList(ProjectFields.Id, ProjectFields.Name, filter);
        }

        public static DataTable GetRunningGeneralProjects()
        {
            var filter = new RelationPredicateBucket(ProjectFields.Status == "جارى");
            filter.PredicateExpression.Add(ProjectFields.IsGeneral == true);//new FieldLikePredicate(ProjectFields.Name, null, "%مشروع عام%"));
            return DALHelper.GetDynamicList(ProjectFields.Id, ProjectFields.Name, filter);
        }

        public static string GetOneKeeperForStore(int storeId)
        {
            var keeperId = DALHelper.GetValue(StorekeeperFields.KeeperId, StorekeeperFields.StoreId, storeId);

            return (string)DALHelper.GetValue(UserFields.Name, UserFields.Id, (int)keeperId);
        }
        [Obsolete]
        public static decimal GetUnitPrice(int itemId, int shelfId)
        {
            var filter = new RelationPredicateBucket();

            filter.PredicateExpression.Add(StoreShelfBalanceFields.ShelfId == shelfId);
            filter.PredicateExpression.Add(StoreShelfBalanceFields.ItemId == itemId);

            var entities = new EntityCollection<StoreShelfBalanceEntity>();
            DALHelper.FetchCollection(entities, filter);
            if (entities.Count > 0 && entities[0].UnitPrice.HasValue)
                return entities[0].UnitPrice.Value;
            return 0;
        }

        public static string GetStoreProjectManager(int storeId)
        {
            var projectId = DALHelper.GetValue(StoreShelfBalanceFields.ProjectId, StoreShelfBalanceFields.StoreId, storeId);

            if (projectId != null)
                return (string)DALHelper.GetValue(ProjectFields.Manager, ProjectFields.Id, (int)projectId);
            return "";
        }


        public static DataTable GetStoresForKeeper()
        {
            return FetchStoresForKeeper(false, false);
        }

        public static DataTable GetJunkStoresForKeeper()
        {
            return FetchStoresForKeeper(true, false);
        }
        public static DataTable GetStoresWithoutTransactions()
        {
            return FetchStoresForKeeper(false, true);
        }
        public static DataTable GetTargetStoresForKeeper(int sourceStoreId)
        {
            return FetchStoresForKeeper(false, false, sourceStoreId);
        }

        private static DataTable FetchStoresForKeeper(bool isJunk, bool withoutTransaction)
        {
            return FetchStoresForKeeper(isJunk, withoutTransaction, 0);
        }
        private static DataTable FetchStoresForKeeper(bool isJunk, bool withoutTransaction, int sourceStoreId)
        {
            var filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(new FieldCompareSetPredicate(StoreFields.Id, null, StorekeeperFields.StoreId, null, SetOperator.In, new PredicateExpression(StorekeeperFields.KeeperId == UserInfo.UserId)));
            filter.PredicateExpression.Add(StoreFields.IsPrimary == false);

            if (isJunk)
            {
                filter.PredicateExpression.Add(StoreFields.Type == "كهنة");
            }
            if (withoutTransaction)
            {
                filter.PredicateExpression.Add(new FieldCompareSetPredicate(StoreFields.Id, null, TransactionItemFields.StoreId, null, SetOperator.In, null, null, null, true));
            }
            if (sourceStoreId > 0)
            {
                //var sourceFilter = new PredicateExpression(StoreFields.Id == sourceStoreId);
                //filter.PredicateExpression.Add(new FieldCompareSetPredicate(StoreFields.ParentStoreId, null, StoreFields.ParentStoreId, null, SetOperator.In, sourceFilter, null, null, false));
                filter.PredicateExpression.Add(StoreFields.Id != sourceStoreId);
            }
            return DALHelper.GetDynamicList(StoreFields.Id, StoreFields.Name, filter);
        }

        /// <summary>
        /// Get stores for Ezn elsarf
        /// </summary>
        /// <param name="isJunk"></param>
        /// <param name="withoutTransaction"></param>
        /// <param name="sourceStoreId"></param>
        /// <returns></returns>
        public static DataTable GetStoresByProjectId(object projectId)
        {
            if (projectId == null) return null;

            var storeShelfBalanceEntities = new EntityCollection<StoreShelfBalanceEntity>();
            var storesFilter = new RelationPredicateBucket();
            storesFilter.PredicateExpression.Add(StoreShelfBalanceFields.ProjectId == (int)projectId);
            DALHelper.FetchCollection(storeShelfBalanceEntities, storesFilter, null, null);
            var ids = storeShelfBalanceEntities.Select(s=>s.StoreId).ToList();

            var filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(new FieldCompareSetPredicate(StoreFields.Id, null, StorekeeperFields.StoreId, null, SetOperator.In, new PredicateExpression(StorekeeperFields.KeeperId == UserInfo.UserId)));
            filter.PredicateExpression.Add(StoreFields.IsPrimary == false);
            
            var dataTable = DALHelper.GetDynamicList(StoreFields.Id, StoreFields.Name, filter);
            var newDataTable = new DataTable();
            newDataTable.Columns.Add("Id");
            newDataTable.Columns.Add("Name");
            foreach (DataRow row in dataTable.Rows)
            {
                var storeId = (int)row["Id"];
                var storeName = (string)row["Name"];

                if (ids.Contains(storeId))
                {
                    var newRow = newDataTable.NewRow();
                    newRow["Id"] = storeId;
                    newRow["Name"] = storeName;
                    newDataTable.Rows.Add(newRow);
                }
            }
            newDataTable.AcceptChanges();
            return newDataTable;
        }
        /// <summary>
        /// Use default argument to send any parameter of them
        /// </summary>
        /// <param name="id">id of supply if exists</param>
        /// <param name="number">number of supply if exists</param>
        /// <returns></returns>
        public static SupplyOrderEntity GetSupplyOrderByIdOrNumber(int id = 0, string number = "")
        {
            var supplyOrderCollection = new EntityCollection<SupplyOrderEntity>();

            if (id != 0)
            {
                DALHelper.FetchCollection(supplyOrderCollection, new RelationPredicateBucket(SupplyOrderFields.Id == id));
            }
            else if (!string.IsNullOrEmpty(number))
            {
                DALHelper.FetchCollection(supplyOrderCollection, new RelationPredicateBucket(SupplyOrderFields.SupplyOrderNumber == number));
            }

            if (supplyOrderCollection.Count > 0)
            {
                return supplyOrderCollection[0];
            }
            return null;
        }

        public static TransferRequestEntity GetTransferRequestByIdOrNumber(int id = 0, string number = "")
        {
            var transferRequestsCollection = new EntityCollection<TransferRequestEntity>();

            if (id != 0)
            {
                DALHelper.FetchCollection(transferRequestsCollection, new RelationPredicateBucket(TransferRequestFields.Id == id));
            }
            else if (!string.IsNullOrEmpty(number))
            {
                DALHelper.FetchCollection(transferRequestsCollection, new RelationPredicateBucket(TransferRequestFields.RequestNumber == number));
            }

            if (transferRequestsCollection.Count > 0)
            {
                return transferRequestsCollection[0];
            }
            return null;
        }

        public static string GetItemUnit(int itemId)
        {
            var result = DALHelper.GetValue(ItemFields.Unit, ItemFields.Id, itemId);
            if (result == null || result == DBNull.Value)
                return string.Empty;

            return (string.IsNullOrEmpty((string)result)) ? string.Empty : (string)result;
        }

        public static decimal GetItemWeight(int itemId)
        {
            var result = DALHelper.GetValue(ItemFields.Weight, ItemFields.Id, itemId);
            if (result == null || result == DBNull.Value)
                return 0;

            return decimal.Parse(result.ToString());
        }
        /// <summary>
        /// This method was made as I did not find any method to get all items fields plus reducing overhead to get all item fields
        /// </summary>
        /// <param name="itemId"></param>
        /// <returns></returns>
        public static string GetItemDescription(int itemId)
        {
            var result = DALHelper.GetValue(ItemFields.Description, ItemFields.Id, itemId);
            if (result == null || result == DBNull.Value)
                return string.Empty;

            return (string.IsNullOrEmpty((string)result)) ? string.Empty : (string)result;
        }

        private static EntityCollection<StoreShelfBalanceEntity> FetchExistingItems(int storeId, int projectId, EntityCollection<TransactionItemEntity> transactionItems)
        {
            var itemIds = (from t in transactionItems
                           select t.ItemId).ToList();

            var existingItems = new EntityCollection<StoreShelfBalanceEntity>();
            var filter = new RelationPredicateBucket(StoreShelfBalanceFields.ItemId == itemIds.ToArray());
            filter.PredicateExpression.Add(StoreShelfBalanceFields.StoreId == storeId);
            if (projectId > 0)
                filter.PredicateExpression.Add(StoreShelfBalanceFields.ProjectId == projectId);

            DALHelper.FetchCollection(existingItems, filter);
            return existingItems;
        }

        //public static bool StoreItems(int storeId, DepositTransactionEntity deposit, TransactionEntity transaction, EntityCollection<TransactionItemEntity> transactionItems)
        public static bool StoreItems(int storeId, DepositTransactionEntity deposit, TransactionEntity transaction)
        {
            //var transactionItems = new EntityCollection<TransactionItemEntity>();
            //DALHelper.FetchCollection(transactionItems, new RelationPredicateBucket(TransactionItemFields.TransactionId == transaction.Id));

            //if (transactionItems.Count <= 0)
            //    return false;
            //var existingItems = FetchExistingItems(storeId, transaction.ProjectId, transactionItems);

            //var storeBalanceCollection = new EntityCollection<StoreShelfBalanceEntity>();
            //foreach (TransactionItemEntity transactionItem in transactionItems)
            //{
            //    //entity.Transaction = transaction;
            //    //entity.StoreId = storeId;

            //    var matchingItem = (from t in existingItems
            //                        where t.ItemId == transactionItem.ItemId && t.ShelfId == transactionItem.ShelfId
            //                        select t).FirstOrDefault();

            //    if (matchingItem == null)
            //        matchingItem = new StoreShelfBalanceEntity();

            //    var originalUnitPrice = (matchingItem.UnitPrice.HasValue)? matchingItem.UnitPrice.Value: (decimal)0;
            //    //var price = (entity.SellingPrice > entity.UnitPrice)?entity.SellingPrice : entity.UnitPrice; 
            //    var price = transactionItem.UnitPrice + transactionItem.LaborCost + transactionItem.Expenses;

            //    matchingItem.UnitPrice = GetAverageUnitPrice(matchingItem.Balance, originalUnitPrice, transactionItem.Quantity, price);
            //    matchingItem.Balance += transactionItem.Quantity;
            //    matchingItem.StoreId = storeId;
            //    matchingItem.ItemId = transactionItem.ItemId;
            //    matchingItem.ShelfId = transactionItem.ShelfId.Value;
            //    matchingItem.ProjectId = transaction.ProjectId;				

            //    storeBalanceCollection.Add(matchingItem);

            //    //set current balance after deposit transaction
            //    transactionItem.CurrentBalance = matchingItem.Balance;
            //}

            ////deposit.Transaction = transaction;

            //var uow = new UnitOfWork2();
            //uow.AddCollectionForSave(storeBalanceCollection);

            //uow.AddCollectionForSave(transactionItems);

            var uow = new UnitOfWork2();
            uow = StoreItems(storeId, transaction);

            if (uow == null)
                return false;

            uow.AddForSave(deposit, null, false, true);

            return DALHelper.SaveUoW(uow);
            //return (DALHelper.SaveCollection(storeBalanceCollection) > 0);
        }

        /// <summary>
        /// Store items in retrieval transaction 
        /// called when the user click on finally save 
        /// </summary>
        /// <param name="storeId"></param>
        /// <param name="retrieval"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static bool StoreItems(int storeId, RetrievalTransactionEntity retrieval, TransactionEntity transaction)
        {
            var uow = new UnitOfWork2();
            uow = StoreItems(storeId, transaction);

            if (uow == null)
                return false;

            uow.AddForSave(retrieval, null, false, true);

            return DALHelper.SaveUoW(uow);
        }

        /// <summary>
        /// Internal method to store items regardless type of transaction Deposit transaction or retrieval transaction 
        /// </summary>
        /// <param name="storeId"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        private static UnitOfWork2 StoreItems(int storeId, TransactionEntity transaction)
        {
            var transactionItems = new EntityCollection<TransactionItemEntity>();
            DALHelper.FetchCollection(transactionItems, new RelationPredicateBucket(TransactionItemFields.TransactionId == transaction.Id));

            if (transactionItems.Count <= 0)
                //return false;
                return null;

            var existingItems = FetchExistingItems(storeId, transaction.ProjectId, transactionItems);

            var storeBalanceCollection = new EntityCollection<StoreShelfBalanceEntity>();
            foreach (TransactionItemEntity transactionItem in transactionItems)
            {
                //entity.Transaction = transaction;
                //entity.StoreId = storeId;

                var matchingItem = (from t in existingItems
                                    where t.ItemId == transactionItem.ItemId && t.ShelfId == transactionItem.ShelfId
                                    select t).FirstOrDefault();

                if (matchingItem == null)
                    matchingItem = new StoreShelfBalanceEntity();

                var originalUnitPrice = (matchingItem.UnitPrice.HasValue) ? matchingItem.UnitPrice.Value : (decimal)0;
                //var price = (entity.SellingPrice > entity.UnitPrice)?entity.SellingPrice : entity.UnitPrice; 
                var price = transactionItem.UnitPrice + transactionItem.LaborCost + transactionItem.Expenses;

                matchingItem.UnitPrice = GetAverageUnitPrice(matchingItem.Balance, originalUnitPrice, transactionItem.Quantity, price);
                matchingItem.Balance += transactionItem.Quantity;
                matchingItem.StoreId = storeId;
                matchingItem.ItemId = transactionItem.ItemId;
                matchingItem.ShelfId = transactionItem.ShelfId.Value;
                matchingItem.ProjectId = transaction.ProjectId;

                storeBalanceCollection.Add(matchingItem);

                //set current balance after deposit transaction
                transactionItem.CurrentBalance = matchingItem.Balance;
            }

            //deposit.Transaction = transaction;

            var uow = new UnitOfWork2();
            uow.AddCollectionForSave(storeBalanceCollection);

            uow.AddCollectionForSave(transactionItems);

            return uow;

            //uow.AddForSave(deposit, null, false, true);

            //return DALHelper.SaveUoW(uow);
            //return (DALHelper.SaveCollection(storeBalanceCollection) > 0);
        }
        private static decimal GetAverageUnitPrice(decimal originalQuanity, decimal originalUnitPrice, decimal addedQuantity, decimal addedUnitPrice)
        {
            if (originalQuanity == 0 || originalUnitPrice == 0)
                return addedUnitPrice;

            var totalValue = (originalQuanity * originalUnitPrice) + (addedQuantity * addedUnitPrice);
            var totalQuantity = originalQuanity + addedQuantity;

            return totalValue / totalQuantity;
        }

        public static bool RemoveItems(int transactionId)
        {
            var transactionItems = new EntityCollection<TransactionItemEntity>();
            DALHelper.FetchCollection(transactionItems, new RelationPredicateBucket(TransactionItemFields.TransactionId == transactionId));

            if (transactionItems.Count <= 0)
                return false;

            var requestIds = (from t in transactionItems
                              select t.RequestItemId.Value).Distinct().ToList();

            var requestItems = new EntityCollection<WithdrawRequestItemEntity>();
            DALHelper.FetchCollection(requestItems, new RelationPredicateBucket(WithdrawRequestItemFields.Id == requestIds));


            var existingItems = FetchExistingItems(transactionItems[0].StoreId, 0, transactionItems);

            var storeBalanceCollection = new EntityCollection<StoreShelfBalanceEntity>();
            var storeBalanceCollectionToDelete = new EntityCollection<StoreShelfBalanceEntity>();
            foreach (TransactionItemEntity transactionItem in transactionItems)
            {
                //entity.Transaction = transaction;
                //entity.StoreId = storeId;
                //entity.UnitPrice = GetItemCost(storeId, (entity.ShelfId == null)? 0: entity.ShelfId.Value , entity.ItemId);

                var requestItem = (from t in requestItems
                                   where t.Id == transactionItem.RequestItemId.Value
                                   select t).FirstOrDefault();

                if (requestItem != null)
                {
                    requestItem.WithdawedQuantity += transactionItem.Quantity;
                }


                var matchingItem = (from t in existingItems
                                    where t.ItemId == transactionItem.ItemId && t.ShelfId == transactionItem.ShelfId
                                    select t).FirstOrDefault();

                if (matchingItem == null)
                    continue;

                if (matchingItem.Balance > transactionItem.Quantity)
                {
                    matchingItem.Balance -= transactionItem.Quantity;
                    storeBalanceCollection.Add(matchingItem);

                    //set the current balance after withdrawal transaction 
                    transactionItem.CurrentBalance = matchingItem.Balance;

                }
                else
                {
                    storeBalanceCollectionToDelete.Add(matchingItem);
                }
            }

            //withdraw.Transaction = transaction;

            var uow = new UnitOfWork2();
            uow.AddCollectionForSave(storeBalanceCollection);

            uow.AddCollectionForSave(transactionItems);
            //uow.AddForSave(withdraw, null, true, true);
            uow.AddCollectionForDelete(storeBalanceCollectionToDelete);
            uow.AddCollectionForSave(requestItems);

            return DALHelper.SaveUoW(uow);
        }
        public static bool MoveItems(int fromStoreId, int toStoreId, TransferTransactionEntity transfer, TransactionEntity transaction, EntityCollection<TransactionItemEntity> sourceTransactionItems)
        {
            var existingSourceItems = FetchExistingItems(fromStoreId, transaction.ProjectId, sourceTransactionItems);
            var existingTargetItems = FetchExistingItems(toStoreId, transaction.ProjectId, sourceTransactionItems);

            var targetTransactionItems = new EntityCollection<TransactionItemEntity>();
            var storeTargetBalanceCollection = new EntityCollection<StoreShelfBalanceEntity>();
            var storeSourceBalanceCollection = new EntityCollection<StoreShelfBalanceEntity>();
            var storeSourceBalanceCollectionToDelete = new EntityCollection<StoreShelfBalanceEntity>();

            foreach (TransactionItemEntity sourceTransactionItem in sourceTransactionItems)
            {
                if (fromStoreId == toStoreId && sourceTransactionItem.ShelfId == sourceTransactionItem.NewShelfId)
                    continue;

                #region managing source
                sourceTransactionItem.Transaction = transaction;
                sourceTransactionItem.StoreId = fromStoreId;
                sourceTransactionItem.MoveType = "OT";

                var matchingSourceItem = (from t in existingSourceItems
                                          where t.ItemId == sourceTransactionItem.ItemId && t.ShelfId == sourceTransactionItem.ShelfId
                                          select t).FirstOrDefault();

                if (matchingSourceItem == null)
                    continue;


                if (matchingSourceItem.Balance > sourceTransactionItem.Quantity)
                {
                    matchingSourceItem.Balance -= sourceTransactionItem.Quantity;
                    storeSourceBalanceCollection.Add(matchingSourceItem);
                }
                else
                {
                    storeSourceBalanceCollectionToDelete.Add(matchingSourceItem);
                }
                #endregion

                #region managing target
                var matchingTargetItem = (from t in storeTargetBalanceCollection
                                          where t.ItemId == sourceTransactionItem.ItemId
                                          && t.ShelfId == sourceTransactionItem.NewShelfId
                                          && t.ProjectId == transaction.ProjectId
                                          select t).FirstOrDefault();

                if (matchingTargetItem == null)
                {
                    matchingTargetItem = (from t in existingTargetItems
                                          where t.ItemId == sourceTransactionItem.ItemId
                                          && t.ShelfId == sourceTransactionItem.NewShelfId
                                          && t.ProjectId == transaction.ProjectId
                                          select t).FirstOrDefault();
                }

                if (matchingTargetItem == null)
                    matchingTargetItem = new StoreShelfBalanceEntity();

                matchingTargetItem.Balance += sourceTransactionItem.Quantity;
                matchingTargetItem.StoreId = toStoreId;
                matchingTargetItem.ItemId = sourceTransactionItem.ItemId;
                matchingTargetItem.ShelfId = sourceTransactionItem.NewShelfId;
                matchingTargetItem.ProjectId = transaction.ProjectId;

                storeTargetBalanceCollection.Add(matchingTargetItem);

                var targetTransactionItem = new TransactionItemEntity()
                {
                    StoreId = toStoreId,
                    ShelfId = sourceTransactionItem.NewShelfId,
                    ItemId = sourceTransactionItem.ItemId,
                    Quantity = sourceTransactionItem.Quantity,
                    MoveType = "IN",
                };
                targetTransactionItem.Transaction = transaction;
                targetTransactionItems.Add(targetTransactionItem);
                #endregion
            }

            transfer.Transaction = transaction;

            var uow = new UnitOfWork2();
            uow.AddCollectionForSave(storeTargetBalanceCollection);
            uow.AddCollectionForSave(storeSourceBalanceCollection);
            uow.AddForSave(transfer, null, false, true);
            uow.AddCollectionForDelete(storeSourceBalanceCollectionToDelete);

            return DALHelper.SaveUoW(uow);
        }
        public static decimal GetItemBalance(int projectId, int storeId, int shelfId, int itemId, bool getItemsOfStoppedProjects)
        {
            var filter = new PredicateExpression(StoreShelfBalanceFields.ItemId == itemId);
            filter.Add(StoreShelfBalanceFields.StoreId == storeId);
            filter.Add(StoreShelfBalanceFields.ShelfId == shelfId);
            //filter.Add(StoreShelfBalanceFields.ProjectId == projectId);

            var projectFilter = new PredicateExpression(StoreShelfBalanceFields.ProjectId == projectId);
            if (getItemsOfStoppedProjects)
            {
                projectFilter.AddWithOr(new FieldCompareSetPredicate(StoreShelfBalanceFields.ProjectId, null, ProjectFields.Id, null, SetOperator.In, new PredicateExpression(ProjectFields.Status == "جارى"), true));
            }
            filter.Add(projectFilter);

            var result = DALHelper.GetValue(StoreShelfBalanceFields.Balance, AggregateFunction.Sum, filter);
            if (result == null || result == DBNull.Value)
                return (decimal)0;

            return (decimal)result;
        }

        public static decimal GetItemBalance(int storeId, int itemId)
        {
            var filter = new PredicateExpression(StoreShelfBalanceFields.ItemId == itemId)
                             {
                                 StoreShelfBalanceFields.StoreId == storeId
                             };

            var result = DALHelper.GetValue(StoreShelfBalanceFields.Balance, AggregateFunction.Sum, filter);
            if (result == null || result == DBNull.Value)
                return 0;

            return (decimal)result;
        }
        public static decimal GetItemPrice(int projectId, int storeId, int shelfId, int itemId)
        {
            var filter = new PredicateExpression(StoreShelfBalanceFields.ItemId == itemId);
            filter.Add(StoreShelfBalanceFields.StoreId == storeId);
            filter.Add(StoreShelfBalanceFields.ShelfId == shelfId);
            filter.Add(StoreShelfBalanceFields.ProjectId == projectId);

            var result = DALHelper.GetValue(StoreShelfBalanceFields.UnitPrice, AggregateFunction.Avg, filter);
            if (result == null || result == DBNull.Value)
                return (decimal)0;

            return (decimal)result;
        }

        /// <summary>
        /// Retrieve unit price of the item from initial balance table
        /// </summary>
        /// <param name="projectId">Store Id</param>
        /// <param name="itemId">Item Id</param>
        /// <returns>Unit price</returns>
        public static decimal GetItemPrice(int storeId, int itemId)
        {
            var filter = new PredicateExpression(InitialBalanceFields.ItemId == itemId);
            filter.Add(InitialBalanceFields.StoreId == storeId);

            var unitPrice = DALHelper.GetValue(InitialBalanceFields.UnitPrice, AggregateFunction.Avg, filter);

            if (unitPrice == null || unitPrice == DBNull.Value)
                return (decimal)0;
            else
                return (decimal)unitPrice;

        }
        public static decimal GetItemCost(int storeId, int shelfId, int itemId)
        {
            var exp = new Expression(TransactionItemFields.UnitPrice, ExOp.Add, TransactionItemFields.LaborCost);
            var totalExp = new Expression(TransactionItemFields.Expenses, ExOp.Add, exp);
            var result = DALHelper.GetValue(TransactionItemFields.UnitPrice.SetExpression(totalExp), AggregateFunction.Avg, GetItemPriceFilter(storeId, shelfId, itemId), GetItemPriceRelations());
            if (result == null || result == DBNull.Value)
                return (decimal)0;

            return (decimal)result;
        }

        private static RelationCollection GetItemPriceRelations()
        {
            var relations = new RelationCollection();
            relations.Add(TransactionItemEntity.Relations.TransactionEntityUsingTransactionId);
            return relations;
        }
        private static PredicateExpression GetItemPriceFilter(int storeId, int shelfId, int itemId)
        {
            var filter = new PredicateExpression(TransactionItemFields.ItemId == itemId);
            filter.Add(TransactionItemFields.StoreId == storeId);
            filter.Add(TransactionItemFields.ShelfId == shelfId);
            filter.Add(TransactionFields.Type == "Deposit");
            return filter;
        }

        public static int GetWithdrawShelfId(int projectId, int storeId, int itemId, decimal remainderQuantity, string withdrawType, out decimal availableBalance, out string shelfCode)
        {
            var shelfId = 0;
            shelfCode = string.Empty;
            availableBalance = 0;

            var balanceItems = new EntityCollection<StoreShelfBalanceEntity>();
            var filter = new RelationPredicateBucket(StoreShelfBalanceFields.StoreId == storeId);
            filter.PredicateExpression.Add(StoreShelfBalanceFields.ItemId == itemId);

            if (withdrawType != "G")
            {
                filter.PredicateExpression.Add(StoreShelfBalanceFields.ProjectId == projectId);
            }

            //switch ()
            //{
            //    case "G":
            //        break;
            //    default:

            //        break;
            //}

            var prefetch = new PrefetchPath2(EntityType.StoreShelfBalanceEntity);
            prefetch.Add(StoreShelfBalanceEntity.PrefetchPathShelf);

            DALHelper.FetchCollection(balanceItems, filter, prefetch, new SortExpression(StoreShelfBalanceFields.Id | SortOperator.Ascending));
            if (balanceItems.Count <= 0)
            {
                return shelfId;
            }


            foreach (StoreShelfBalanceEntity item in balanceItems)
            {
                if (item.Balance >= remainderQuantity)
                {
                    availableBalance = item.Balance;
                    if (item.Shelf != null)
                    {
                        shelfCode = item.Shelf.Code;
                    }
                    return item.ShelfId;
                }
            }

            var balanceView = new EntityView2<StoreShelfBalanceEntity>(balanceItems);
            balanceView.Sorter = new SortExpression(StoreShelfBalanceFields.Balance | SortOperator.Descending);
            var balanceItem = balanceView[0];
            availableBalance = balanceItem.Balance;
            return balanceItem.ShelfId;
        }

        public static UnitEntity GetItemUnitType(int itemId)
        {
            var collection = new EntityCollection<UnitEntity>();
            var filter = new RelationPredicateBucket(ItemFields.Id == itemId);
            filter.Relations.Add(UnitEntity.Relations.ItemEntityUsingUnitId);

            var prefetch = new PrefetchPath2(EntityType.UnitEntity);//.UnitConverterEntity);
            prefetch.Add(UnitEntity.PrefetchPathFromUnitConverters).SubPath.Add(UnitConverterEntity.PrefetchPathFromUnit);

            DALHelper.FetchCollection(collection, filter, prefetch);

            if (collection.Count <= 0)
                return new UnitEntity();

            return collection[0];


            //Converters = new EntityCollection<UnitConverterEntity>();

            //var convertersFilter = new RelationPredicateBucket(UnitConverterFields.ToUnitId == unitType.Id);
            //var prefetch = new PrefetchPath2(EntityType.UnitConverterEntity);
            //prefetch.Add(UnitConverterEntity.PrefetchPathFromUnit);
            //DALHelper.FetchCollection(converters, convertersFilter, prefetch);

            //return unit;
        }

        public static DataTable GetUncodedTransactions()
        {
            var filter = new RelationPredicateBucket(DepositTransactionFields.IsCoded == false);
            filter.Relations.Add(TransactionEntity.Relations.DepositTransactionEntityUsingTransactionId);

            return DALHelper.GetDynamicList(TransactionFields.Id, TransactionFields.PermitNumber, filter, new SortExpression(TransactionFields.PermitNumber | SortOperator.Ascending));
        }
        public static DataTable GetUnpricedDepositTransactions()
        {
            var filter = new RelationPredicateBucket(DepositTransactionFields.IsPriced == false);
            filter.Relations.Add(TransactionEntity.Relations.DepositTransactionEntityUsingTransactionId);

            return DALHelper.GetDynamicList(TransactionFields.Id, TransactionFields.PermitNumber, filter, new SortExpression(TransactionFields.PermitNumber | SortOperator.Ascending));
        }
        public static DataTable GetUnpricedWithdrawalTransactions()
        {
            var filter = new RelationPredicateBucket(WithdrawTransactionFields.IsPriced == false);
            filter.PredicateExpression.Add(WithdrawTransactionFields.TransactionStatus == 1);
            filter.Relations.Add(TransactionEntity.Relations.WithdrawTransactionEntityUsingTransactionId);

            return DALHelper.GetDynamicList(TransactionFields.Id, TransactionFields.PermitNumber, filter, new SortExpression(TransactionFields.PermitNumber | SortOperator.Ascending));
        }

        public static void CodeDone(int transactionId)
        {
            var entity = new DepositTransactionEntity();
            entity.IsCoded = true;
            DALHelper.UpdateEntities(entity, new RelationPredicateBucket(DepositTransactionFields.TransactionId == transactionId));
        }
        public static void PricingDepositsDone(int transactionId)
        {
            var entity = new DepositTransactionEntity();
            entity.IsPriced = true;
            entity.TransactionStatus = 2;
            DALHelper.UpdateEntities(entity, new RelationPredicateBucket(DepositTransactionFields.TransactionId == transactionId));
            //var transactions = new EntityCollection<TransactionEntity>();
            //var filter = new RelationPredicateBucket(TransactionFields.Id == transactionId);
            //var prefetch = new PrefetchPath2(EntityType.TransactionEntity);
            //prefetch.Add(TransactionEntity.PrefetchPathDepositTransactions);
            //prefetch.Add(TransactionEntity.PrefetchPathTransactionItems);

            //DALHelper.FetchCollection(transactions, filter, prefetch);
            //if (transactions.Count <=0 || transactions[0].DepositTransactions.Count <= 0 || transactions[0].TransactionItems.Count <= 0)
            //    return false;

            //transactions[0].DepositTransactions[0].IsPriced = true;
            //transactions[0].DepositTransactions[0].TransactionStatus = 2;

            //var storeId = (transactions[0].StoreId.HasValue)? transactions[0].StoreId.Value: transactions[0].TransactionItems[0].StoreId;

            //return StoreItems(storeId, transactions[0].DepositTransactions[0], transactions[0], transactions[0].TransactionItems);
        }

        public static void PricingWithdrawalsDone(int transactionId)
        {
            //RemoveItems(transactionId);

            var entity = new WithdrawTransactionEntity();
            entity.IsPriced = true;
            entity.TransactionStatus = 2;
            DALHelper.UpdateEntities(entity, new RelationPredicateBucket(WithdrawTransactionFields.TransactionId == transactionId));
        }

        public static ITypedListLgp2 GetDepositItems(int transactionId)
        {
            var list = new DepositItemsTypedList();
            var sorter = new SortExpression(StoreFields.Code | SortOperator.Ascending);
            sorter.Add(ShelfFields.Code | SortOperator.Ascending);
            sorter.Add(ItemFields.Code | SortOperator.Ascending);

            DALHelper.GetTypedList(list, new PredicateExpression(TransactionItemFields.TransactionId == transactionId), sorter);
            return list;
        }

        public static void RefreshStoreOccupancyRate()
        {
            using (var adapter = new DataAccessAdapter(true))
            {
                var exp = new Expression(StoreShelfBalanceFields.Balance, ExOp.Mul, ItemFields.Volume);

                var fields = new ResultsetFields(2);
                fields.DefineField(StoreShelfBalanceFields.StoreId, 0);
                fields.DefineField(StoreShelfBalanceFields.Balance.SetExpression(exp), 1, "Volume", AggregateFunction.Sum);

                var filter = new RelationPredicateBucket();
                filter.Relations.Add(StoreShelfBalanceEntity.Relations.ItemEntityUsingItemId);

                var group = new GroupByCollection(StoreShelfBalanceFields.StoreId);

                var dt = new DataTable();
                adapter.FetchTypedList(fields, dt, filter, 0, null, false, group);

                var stores = new EntityCollection<StoreEntity>();
                adapter.FetchEntityCollection(stores, null);

                foreach (var store in stores)
                {
                    var results = (from row in dt.AsEnumerable()
                                   where row.Field<int>("StoreId") == store.Id
                                   select row.Field<decimal>("Volume")).FirstOrDefault();

                    if (results == 0 || store.TotalSize <= 0)
                    {
                        store.OccupancyRate = 0;
                        continue;
                    }

                    store.OccupancyRate = (results / store.TotalSize) * 100;
                }

                adapter.SaveEntityCollection(stores);
            }
        }
        public static void CleanStoreParentRelations()
        {
            var filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(new FieldCompareSetPredicate(StoreFields.ParentStoreId, null, StoreFields.Id, null, SetOperator.In, new PredicateExpression(StoreFields.IsPrimary == false)));

            var entity = new StoreEntity();
            entity.ParentStoreId = null;

            DALHelper.UpdateEntities(entity, filter);
        }

        public static bool CheckExisitngPermitNumber(string permitNumber, string type)
        {
            var filter = new RelationPredicateBucket(TransactionFields.PermitNumber == permitNumber);
            filter.PredicateExpression.Add(TransactionFields.Type == type);

            return (DALHelper.GetCount(new EntityCollection<TransactionEntity>(), filter)) > 0;
        }

        public static bool CheckExisitngPermitNumber(string permitNumber, string type, int id)
        {
            var filter = new RelationPredicateBucket(TransactionFields.PermitNumber == permitNumber);
            filter.PredicateExpression.Add(TransactionFields.Type == type);
            filter.PredicateExpression.Add(TransactionFields.Id != id);

            return (DALHelper.GetCount(new EntityCollection<TransactionEntity>(), filter)) > 0;
        }

        public static bool CheckExisitngWithdrawRequestNumber(string requestNumber)
        {
            var filter = new RelationPredicateBucket(WithdrawRequestFields.RequestNumber == requestNumber);

            return (DALHelper.GetCount(new EntityCollection<WithdrawRequestEntity>(), filter)) > 0;
        }

        public static bool CheckExisitngWithdrawRequestNumber(string requestNumber, int id)
        {
            var filter = new RelationPredicateBucket(WithdrawRequestFields.RequestNumber == requestNumber);
            filter.PredicateExpression.Add(WithdrawRequestFields.Id != id);

            return (DALHelper.GetCount(new EntityCollection<WithdrawRequestEntity>(), filter)) > 0;
        }

        public static bool CheckExisitngTransferRequestNumber(string requestNumber)
        {
            var filter = new RelationPredicateBucket(TransferRequestFields.RequestNumber == requestNumber);

            return (DALHelper.GetCount(new EntityCollection<TransferRequestEntity>(), filter)) > 0;
        }

        public static bool CheckExisitngTransferRequestNumber(string requestNumber, int id)
        {
            var filter = new RelationPredicateBucket(TransferRequestFields.RequestNumber == requestNumber);
            filter.PredicateExpression.Add(TransferRequestFields.Id != id);

            return (DALHelper.GetCount(new EntityCollection<TransferRequestEntity>(), filter)) > 0;
        }
        public static bool CheckExisitngSupplyOrderNumber(string orderNumber, int id)
        {
            var filter = new RelationPredicateBucket(SupplyOrderFields.SupplyOrderNumber == orderNumber);
            filter.PredicateExpression.Add(SupplyOrderFields.Id != id);

            return (DALHelper.GetCount(new EntityCollection<SupplyOrderEntity>(), filter)) > 0;
        }


        public static ProjectEntity GetProjectById(int id)
        {
            DataAccessAdapter adaper = new DataAccessAdapter();

            var filter = new RelationPredicateBucket(ProjectFields.Id == id);
            EntityCollection<ProjectEntity> project = new EntityCollection<ProjectEntity>();
            adaper.FetchEntityCollection(project, filter);
            if (project.Count > 0)
                return project[0];
            return null;
        }

        public static ProjectEntity GetProjectByCode(string projectCode)
        {
            DataAccessAdapter adaper = new DataAccessAdapter();

            var filter = new RelationPredicateBucket(ProjectFields.Code == projectCode);
            EntityCollection<ProjectEntity> project = new EntityCollection<ProjectEntity>();
            adaper.FetchEntityCollection(project, filter);
            if (project.Count > 0)
                return project[0];
            return null;
        }

        public static DataTable GetStoreBalance(int storeId)
        {

            return GetStoreBalance(storeId, null);
        }

        /// <summary>
        /// Retrieve all shelves that contain specific item for specific project
        /// </summary>
        /// <param name="storeId"></param>
        /// <param name="itemId"></param>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public static DataTable GetItemStoreShelfBalance(int storeId, int itemId, int transcationId)
        {
            DataTable StoreShelfBalanceDT = new DataTable();

            var fields = new ResultsetFields(2);
            fields.DefineField(TransactionItemFields.ShelfId, 0);
            fields.DefineField(TransactionItemFields.Quantity, 1);

            var filter = new RelationPredicateBucket(TransactionItemFields.StoreId == storeId & TransactionItemFields.ItemId == itemId & TransactionItemFields.TransactionId == transcationId);

            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(fields, StoreShelfBalanceDT, filter);
            }

            return StoreShelfBalanceDT;

            //var fields = new ResultsetFields(2);
            //fields.DefineField(StoreShelfBalanceFields.ShelfId, 0);
            //fields.DefineField(StoreShelfBalanceFields.Balance, 1);


            //var storeItems = new EntityCollection<StoreShelfBalanceEntity>();
            //var filter = new RelationPredicateBucket(StoreShelfBalanceFields.StoreId == storeId & StoreShelfBalanceFields.ItemId==itemId & StoreShelfBalanceFields.ProjectId==projectId);

            //using (var adapter = new DataAccessAdapter())
            //{
            //    adapter.FetchTypedList(fields,StoreShelfBalanceDT, filter);
            //}

            //return StoreShelfBalanceDT; 
        }
        /// <summary>
        /// Retrieve the balance of each item inside specific store till specific end date
        /// </summary>
        /// <param name="storeId">ID of given store </param>
        /// <param name="selectedEndDate">Selected end dateTime </param>
        /// <returns></returns>
        public static DataTable GetStoreBalance(int storeId, DateTime? selectedEndDate)
        {
            DataTable storeItemsDT = new DataTable();

            storeItemsDT.Columns.Add(new DataColumn("Code"));
            storeItemsDT.Columns.Add(new DataColumn("Name"));
            storeItemsDT.Columns.Add(new DataColumn("Unit"));
            storeItemsDT.Columns.Add(new DataColumn("Balance"));
            storeItemsDT.Columns.Add(new DataColumn("Value"));

            var storeItems = new EntityCollection<StoreShelfBalanceEntity>();
            var filter = new RelationPredicateBucket(StoreShelfBalanceFields.StoreId == storeId);

            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(storeItems, filter);
            }

            decimal itemBalanceInStore;
            int index = 0;
            foreach (int itemId in storeItems.Select(s => s.ItemId).ToList())
            {
                itemBalanceInStore = GetItemBalance(itemId, storeId, selectedEndDate);

                //Get item unit price from initial balance table
                decimal unitPrice = GetItemPrice(storeId, itemId);

                DataTable itemDT = Business.GetItemDetails(itemId);
                string productCode = itemDT.Rows[0][0].ToString();
                string productName = itemDT.Rows[0][1].ToString();
                string unitType = itemDT.Rows[0][2].ToString();

                //double value = (double)itemBalanceInStore * (double)storeItems[index].UnitPrice.Value;
                double value;
                if (unitPrice != 0)
                {
                    value = (double)itemBalanceInStore * (double)unitPrice;
                }
                else
                {
                    value = (double)itemBalanceInStore * (double)storeItems[index].UnitPrice.Value;
                }

                storeItemsDT.Rows.Add(productCode, productName, unitType, itemBalanceInStore, value);
                index++;
            }

            return storeItemsDT;
        }
        public static void GetLookup(IEntityCollection2 collection, EntityField2 idField, EntityField2 codeField, EntityField2 nameField)
        {
            //var dt = new DataTable();
            var fields = new ResultsetFields(2);
            fields.DefineField(idField, 0);
            fields.DefineField(ItemFields.Name, 1);

            fields[1].ExpressionToApply = GetCodeNameExpression(codeField, nameField);
            var sorter = new SortExpression(codeField | SortOperator.Ascending);

            List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
            valueProjectors.Add(new DataValueProjector(idField.ToString(), 0, typeof(int)));
            valueProjectors.Add(new DataValueProjector(nameField.ToString(), 1, typeof(string)));

            var projector = new DataProjectorToIEntityCollection2(collection);

            var dt = new DataTable();
            using (var adapter = new DataAccessAdapter())
            {
                //adapter.FetchTypedList(fields, dt, null, 0, sorter, false, null);
                adapter.FetchProjection(valueProjectors, projector, fields, null, 0, sorter, true);
            }

            //return dt;
        }

        /// <summary>
        /// Retrieve Item balance for specific Item inside specific 
        /// </summary>
        /// <param name="itemId"></param>
        /// <param name="storeId"></param>
        /// <param name="selectedEndDate"></param>
        /// <returns></returns>
        public static decimal GetItemBalance(int itemId, int storeId, DateTime? selectedEndDate)
        {

            return GetItemCard(itemId, storeId, null, selectedEndDate).Last().AccumulatedQuantity;
        }
        public static DataTable GetItemBalance(int itemId)
        {
            var dt = new DataTable();
            var fields = new ResultsetFields(3);
            fields.DefineField(StoreFields.Code, 0, "StoreCode");
            fields.DefineField(StoreFields.Name, 1, "StoreName");
            fields.DefineField(StoreShelfBalanceFields.Balance, 2, "Balance", AggregateFunction.Sum);

            var groupBy = new GroupByCollection(fields[0]);
            groupBy.Add(fields[1]);


            var filter = new RelationPredicateBucket(StoreShelfBalanceFields.ItemId == itemId);
            filter.Relations.Add(StoreShelfBalanceEntity.Relations.StoreEntityUsingStoreId);

            var sorter = new SortExpression((EntityField2)fields[0] | SortOperator.Ascending);

            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(fields, dt, filter, 0, sorter, false, groupBy);
            }

            return dt;
        }

        public static void GetItemview(ItemViewTypedView items)
        {
            if (items == null)
                items = new ItemViewTypedView();
            else
                items.Clear();

            DALHelper.GetTypedView(items, null, new SortExpression(ItemViewFields.NameCodeUnit | SortOperator.Ascending));
        }

        public static int PopulateInventory(int storeId, DateTime inventoryDate)
        {
            var inventory = new InventoryEntity() { CountDate = inventoryDate, StoreId = storeId };

            var fields = new ResultsetFields(3);
            fields.DefineField(StoreShelfBalanceFields.ShelfId, 0);
            fields.DefineField(StoreShelfBalanceFields.ItemId, 1);
            fields.DefineField(StoreShelfBalanceFields.Balance, 2, AggregateFunction.Sum);

            var groupBy = new GroupByCollection();
            groupBy.Add(fields[0]);
            groupBy.Add(fields[1]);

            var storedItems = new DataTable();
            using (var adapter = new DataAccessAdapter(true))
            {
                adapter.FetchTypedList(fields, storedItems, new RelationPredicateBucket(StoreShelfBalanceFields.StoreId == storeId), 0, null, false, groupBy);

                var inventoryItems = new EntityCollection<InventoryItemEntity>();

                foreach (DataRow dataRow in storedItems.Rows)
                {
                    var inventoryItem = new InventoryItemEntity()
                    {
                        Inventory = inventory,
                        ShelfId = (int)dataRow[0],
                        ItemId = (int)dataRow[1],
                        Quantitystored = (decimal)dataRow[2]
                    };
                }

                adapter.SaveEntity(inventory, false, true);
            }

            return inventory.Id;
        }

        public static IExpression GetItemNameExpression()
        {
            return new DbFunctionCall("{0} + '  -  ' + {1} + '  -  ' + ({2})", new object[] { ItemFields.Name, ItemFields.Code, ItemFields.Unit });
        }
        public static IExpression GetCodeNameExpression(EntityField2 codeField, EntityField2 nameField)
        {
            return new DbFunctionCall("CAST({0} AS NVARCHAR) + '  -  ' + {1}", new object[] { codeField, nameField });
        }

        public static DataTable GetDepositReport(int storeId, DateTime fromDate, DateTime toDate)
        {
            return GetTransactionItemReport(storeId, fromDate, toDate, "Deposit", "IN");
        }
        public static DataTable GetWithdrawalReport(int storeId, DateTime fromDate, DateTime toDate)
        {
            return GetTransactionItemReport(storeId, fromDate, toDate, "Withdraw", "OT");
        }

        private static DataTable GetTransactionItemReport(int storeId, DateTime fromDate, DateTime toDate, string transactionType, string moveType)
        {
            var dt = new DataTable();
            var fields = new ResultsetFields(8);
            fields.DefineField(ItemFields.Code, 0);
            fields.DefineField(ItemFields.Name, 1);
            //fields.DefineField(ItemFields.Unit, 2);
            fields.DefineField(UnitFields.Name, 2, "Unit");
            fields.DefineField(TransactionFields.Date, 3);
            //fields.DefineField(TransactionItemFields.Quantity, 4, AggregateFunction.Sum);
            fields.DefineField(TransactionItemFields.Quantity, 4);
            fields.DefineField(TransactionItemFields.UnitPrice, 5, AggregateFunction.Max);
            fields.DefineField(TransactionItemFields.Id.SetExpression(new Expression(fields[4], ExOp.Mul, fields[5])), 6, "TotalPrice");
            fields.DefineField(TransactionFields.PermitNumber, 7);

            var groupBy = new GroupByCollection(fields[0]);
            groupBy.Add(fields[1]);
            groupBy.Add(fields[2]);
            groupBy.Add(fields[3]);
            groupBy.Add(fields[4]);
            groupBy.Add(fields[7]);

            var filter = new RelationPredicateBucket(TransactionItemFields.StoreId == storeId);
            //filter.PredicateExpression.Add(TransactionFields.Date.SetExpression(new DbFunctionCall("DATEPART(dy, {0})", new object[] { TransactionFields.Date })) == fromDate.DayOfYear);
            filter.PredicateExpression.Add(new FieldBetweenPredicate(TransactionFields.Date, null, fromDate, toDate));

            var moveFilter = new PredicateExpression(TransactionFields.Type == "Move" & TransactionItemFields.MoveType == moveType);

            var typeFilter = new PredicateExpression(TransactionFields.Type == transactionType);
            typeFilter.AddWithOr(moveFilter);

            filter.PredicateExpression.Add(typeFilter);

            filter.Relations.Add(TransactionEntity.Relations.TransactionItemEntityUsingTransactionId);
            filter.Relations.Add(TransactionItemEntity.Relations.ItemEntityUsingItemId);
            filter.Relations.Add(ItemEntity.Relations.UnitEntityUsingUnitId);

            var sorter = new SortExpression((EntityField2)fields[3] | SortOperator.Ascending);

            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(fields, dt, filter, 0, sorter, false, groupBy);
            }

            return dt;
        }

        public static List<ItemCard> GetItemCard(int itemId, int storeId, DateTime? startDate, DateTime? endDate)
        {
            var cards = new List<ItemCard>();

            var prefetch = new PrefetchPath2(EntityType.TransactionItemEntity);
            var prefetchElement = prefetch.Add(TransactionItemEntity.PrefetchPathTransaction);

            prefetchElement.SubPath.Add(TransactionEntity.PrefetchPathTransferTransactions);
            prefetchElement.SubPath.Add(TransactionEntity.PrefetchPathDepositTransactions, new ExcludeIncludeFieldsList(false, new List<EntityField2>() { DepositTransactionFields.Id }))
                .SubPath.Add(DepositTransactionEntity.PrefetchPathSupplier, new ExcludeIncludeFieldsList(false, new List<EntityField2>() { SupplierFields.AccountingNumber, SupplierFields.Name }));
            prefetchElement.SubPath.Add(TransactionEntity.PrefetchPathWithdrawTransactions, new ExcludeIncludeFieldsList(false, new List<EntityField2>() { WithdrawTransactionFields.Id }))
                .SubPath.Add(WithdrawTransactionEntity.PrefetchPathContractor, new ExcludeIncludeFieldsList(false, new List<EntityField2>() { ContractorFields.AccountingNumber, ContractorFields.Name }));


            var filter = new RelationPredicateBucket(TransactionItemFields.StoreId == storeId);
            filter.PredicateExpression.Add(TransactionItemFields.ItemId == itemId);

            if (endDate != null)
                filter.PredicateExpression.Add(TransactionFields.Date <= endDate);

            filter.Relations.Add(TransactionItemEntity.Relations.TransactionEntityUsingTransactionId);
            filter.Relations.Add(TransactionEntity.Relations.ProjectEntityUsingProjectId);


            var sorter = new SortExpression(TransactionFields.Date | SortOperator.Ascending);
            sorter.Add(TransactionFields.Id | SortOperator.Ascending);
            sorter.Add(TransactionItemFields.Id | SortOperator.Ascending);

            var transactionItems = new EntityCollection<TransactionItemEntity>();

            DALHelper.FetchCollection(transactionItems, filter, prefetch, sorter);


            decimal initialQuantity = 0;
            decimal initialPrice = 0;
            GetItemInitialBalance(itemId, storeId, out initialQuantity, out initialPrice);
            decimal initialTotal = initialQuantity * initialPrice;

            decimal currentQuantity = 0;
            decimal currentTotal = 0;

            //The following 3 properties represent startup balance "الرصيد الإفتتاحي"
            decimal startupQuantity = initialQuantity;
            decimal startupPrice = initialPrice;
            decimal startupTotal = initialTotal;

            if (startDate == null)
                startDate = new DateTime();

            currentQuantity = initialQuantity;
            currentTotal = initialTotal;

            decimal allInQuantity = initialQuantity;
            decimal allInTotal = initialTotal;

            decimal allOutQuantity = 0;
            decimal allOutTotal = 0;

           
            foreach (var transItem in transactionItems)
            {
                initialPrice = transItem.UnitPrice;
                //the following condition is responsible for to calculating (رصيد اول المده)
                if (transItem.Transaction.Date < startDate)
                {
                    var type = transItem.Transaction.Type;

                    if (type == "Deposit" || (type == "Move" && transItem.MoveType == "IN") || type == "Retrieve")
                    {
                        initialQuantity += transItem.Quantity;

                        initialTotal += ((transItem.Quantity * initialPrice));


                        allInTotal += ((transItem.Quantity * initialPrice));
                        allInQuantity += initialQuantity += transItem.Quantity;

                    }
                    else if (type == "Withdraw" || (type == "Move" && transItem.MoveType == "OT"))
                    {
                        initialQuantity -= transItem.Quantity;

                        initialTotal -= ((transItem.Quantity * initialPrice));

                        allOutTotal += ((transItem.Quantity * initialPrice));
                        allOutQuantity += initialQuantity += transItem.Quantity;


                    }


                    currentQuantity = initialQuantity;
                    currentTotal = initialTotal;

                    continue;
                }



                cards.Add(FillItemCard(transItem, initialPrice, ref currentQuantity, ref currentTotal,
                    ref allInQuantity, ref allInTotal, ref allOutQuantity, ref allOutTotal));
            }

            //Add the startup balance
            cards.Insert(0, new ItemCard()
            {
                Comment = "رصيد افتتاحي",
                //Date = "",
                InPrice = startupPrice,
                InQuantity = startupQuantity,
                InTotal = startupTotal,
                AccumulatedQuantity = startupQuantity,
                AccumulatedTotal = startupTotal,
                //PermitNumber = "Opening"
            });


            //add the first data balance that means (startup balance + all balances before the start date )-----------------Very Important to be Uncommeneted next year
            //cards.Insert(1, new ItemCard()
            //{
            //    Comment = "رصيد اول المده",
            //    Date = startDate.Value,
            //    InPrice = initialPrice,
            //    InQuantity = initialQuantity,
            //    InTotal = initialTotal,
            //    AccumulatedQuantity = initialQuantity,
            //    AccumulatedTotal = initialTotal,
            //    AllInPrice = allInTotal,
            //    AllInQuantity = allInQuantity,
            //    AllOutPrice = allOutTotal,
            //    AllOutQuantity = allOutQuantity,
            //    //PermitNumber = "First"
            //});

            //cards.Insert(cards.Count, new ItemCard() { AllInPrice = allInTotal, AllInQuantity = allInQuantity, 
            //    AllOutPrice = allOutTotal, AllOutQuantity = allOutQuantity });
            
            //from crd in cards
            //    group crd by crd.TransactionId into cardsGrouped
            //        select new ItemCard
            //                   {
            //                       InQuantity = cardsGrouped.Sum(x => x.InQuantity),
            //                       OutQuantity = cardsGrouped.Sum(x => x.OutQuantity),
            //                       InPrice = cardsGrouped.Sum(x => x.OutQuantity),
            //                       OutPrice = cardsGrouped.Sum(x => x.outpr)
            //                   }

            List<ItemCard> result = new List<ItemCard>();
            ItemCard card;
            foreach (var item in cards.GroupBy(c =>new { x=c.TransactionId,y=c.TransType })) //  c.TransactionId).ToList())
            {
                card = item.ToList().First();
                card.InQuantity = item.ToList().Sum(x => x.InQuantity);
                card.OutQuantity = item.ToList().Sum(x => x.OutQuantity);

                card.InPrice = item.ToList().First().InPrice; // .First(x => x.InPrice));
                card.OutPrice = item.ToList().First().OutPrice; // .Sum(x => x.OutPrice));

                decimal accQty = item.ToList().First().AccumulatedQuantity;
                decimal price = item.ToList().First().AccumulatedTotal;
                if(card.InQuantity>0)
                {
                    accQty += item.ToList().Skip(1).ToList().Sum(x => x.InQuantity);
                    price = item.ToList().First().InPrice;
                    card.AccumulatedTotal += item.ToList().Skip(1).ToList().Sum(x => x.InQuantity) * price;
                }
                if(card.OutQuantity > 0 )
                {
                    accQty -= item.ToList().Skip(1).ToList().Sum(x => x.OutQuantity);
                    price = item.ToList().First().OutPrice;
                    card.AccumulatedTotal -= item.ToList().Skip(1).ToList().Sum(x => x.OutQuantity) * price;
                }
                card.AccumulatedQuantity = accQty;
                //card.AccumulatedTotal = accQty*price;

                card.InTotal = card.InPrice*card.InQuantity;
                card.OutTotal = card.OutPrice*card.OutQuantity;

                //card.AllInQuantity = item.First().AllInQuantity;
                //card.AllOutQuantity = item.ToList().First().AllOutQuantity;

                //card.AllInPrice = item.First().AllInPrice;
                //card.AllOutPrice = item.First().AllOutPrice;

                result.Add(card);
            }
            return result;
        }

        /// <summary>
        /// Get item supplier for use in Examination document as beginning
        /// </summary>
        /// <param name="supplyOrderId"></param>
        /// <returns>Supplier name</returns>
        public static string GetSupplyOrderSupplierName(int supplyOrderId)
        {
            var result = DALHelper.GetValue(SupplyOrderFields.SupplierId, SupplyOrderFields.Id, supplyOrderId);
            if (result == null || result == DBNull.Value)
                return string.Empty;

            var supplierId = (int)result;

            if (supplierId == 0)
                return string.Empty;

            var supplierName = DALHelper.GetValue(SupplierFields.Name, SupplierFields.Id, supplierId);
            if (supplierName == null || supplierName == DBNull.Value)
                return string.Empty;

            return (string)supplierName;
        }

        private static ItemCard FillItemCard(TransactionItemEntity transItem, decimal itemUnitPrice, ref decimal currentQuantity, ref decimal currentTotal,
             ref decimal allInQuantity, ref decimal allInTotal, ref decimal allOutQuantity, ref decimal allOutTotal)
        {

            var card = new ItemCard()
                           {
                               Date = transItem.Transaction.Date,
                               PermitNumber = transItem.Transaction.PermitNumber,
                               TransactionId = transItem.Transaction.Id
                           };

            var type = transItem.Transaction.Type;

            if (type == "Deposit" || (type == "Move" && transItem.MoveType == "IN"))
            {
                if (transItem.Transaction.DepositTransactions.Count > 0)
                {
                    if (transItem.Transaction.DepositTransactions[0].Supplier != null)
                        card.AccountingNumber = transItem.Transaction.DepositTransactions[0].Supplier.AccountingNumber;

                    //card.AccountingTransDate = transItem.Transaction.DepositTransactions[0].;
                }
                if (type == "Deposit")
                {
                    if (transItem.Transaction.DepositTransactions != null && transItem.Transaction.DepositTransactions.Count > 0
                    && transItem.Transaction.DepositTransactions[0] != null && transItem.Transaction.DepositTransactions[0].Supplier != null)
                    {
                        card.Comment = transItem.Transaction.DepositTransactions[0].Supplier.Name;
                    }
                    else
                    {
                        card.Comment = "وارد مشتريات";
                    }
                }
                else if (type == "Move")
                {
                    card.Comment = "وارد تحويلات";
                }
                card.TransType = "Deposit";
                //card.Comment = (type == "Deposit") ? "وارد مشتريات" : "وارد تحويلات";
                //card.InPrice = transItem.UnitPrice;
                card.InPrice = itemUnitPrice;
                card.InQuantity = transItem.Quantity;
                //card.InTotal = transItem.UnitPrice * transItem.Quantity;
                card.InTotal = itemUnitPrice * transItem.Quantity;

                currentQuantity += transItem.Quantity;
                currentTotal += card.InTotal;

                allInQuantity += transItem.Quantity;
                allInTotal += card.InTotal;


            }
            else if (type == "Withdraw" || (type == "Move" && transItem.MoveType == "OT"))
            {
                if (transItem.Transaction.WithdrawTransactions.Count > 0 && transItem.Transaction.WithdrawTransactions[0].Contractor != null)
                    card.AccountingNumber = transItem.Transaction.WithdrawTransactions[0].Contractor.AccountingNumber;

                if (type == "Withdraw")
                {
                    if (transItem.Transaction.WithdrawTransactions != null && transItem.Transaction.WithdrawTransactions.Count > 0
                    && transItem.Transaction.WithdrawTransactions[0] != null && transItem.Transaction.WithdrawTransactions[0].Contractor != null)
                    {
                        card.Comment = transItem.Transaction.WithdrawTransactions[0].Contractor.Name;
                    }
                    else
                    {
                        card.Comment = "منصرف للمقاولين";
                    }
                }
                else if (type == "Move")
                {
                    card.Comment = "منصرف تحويلات";
                }
                card.TransType = "Withdraw";
                //card.Comment = (type == "Withdraw") ?"منصرف للمقاولين":"منصرف تحويلات";

                //card.OutPrice = transItem.UnitPrice;
                card.OutPrice = itemUnitPrice;
                card.OutQuantity = transItem.Quantity;
                //card.OutTotal = transItem.UnitPrice * transItem.Quantity;
                card.OutTotal = itemUnitPrice * transItem.Quantity;

                currentQuantity -= transItem.Quantity;
                currentTotal -= card.OutTotal;

                allOutQuantity += transItem.Quantity;
                allOutTotal += card.OutTotal;


            }
            else if (type == "Retrieve")
            {
                card.Comment = "مرتجعات";
                card.TransType = "Retrieve";
                //card.OutPrice = transItem.UnitPrice;
                card.InPrice = itemUnitPrice;
                card.InQuantity = transItem.Quantity;
                //card.OutTotal = transItem.UnitPrice * transItem.Quantity;
                card.InTotal = itemUnitPrice * transItem.Quantity;

                currentQuantity += transItem.Quantity;
                currentTotal += card.InTotal;

                allInQuantity += transItem.Quantity;
                allInTotal += card.InTotal;

            }

            card.AccumulatedQuantity = currentQuantity;
            card.AccumulatedTotal = currentTotal;

            return card;
        }

        private static void GetItemInitialBalance(int itemId, int storeId, out decimal initialQuantity, out decimal initialPrice)
        {
            initialQuantity = 0;
            initialPrice = 0;

            decimal totlaValue = 0;


            //Modify the code to read from the new table initial balancee table use the old approach get value from StoreShelfBalance
            //and if the item not exist at initial balanc
            var itemInitialBalances = new EntityCollection<InitialBalanceEntity>();
            var filterInitialBalance = new RelationPredicateBucket(InitialBalanceFields.StoreId == storeId);
            filterInitialBalance.PredicateExpression.Add(InitialBalanceFields.ItemId == itemId);
            DALHelper.FetchCollection(itemInitialBalances, filterInitialBalance);

            if (itemInitialBalances.Count > 0)
            {
                foreach (var item in itemInitialBalances)
                {
                    initialQuantity += item.Balance;
                    totlaValue = (item.Balance * item.UnitPrice.Value);
                }
            }
            //else
            //{
            //    var itemBalances = new EntityCollection<StoreShelfBalanceEntity>();
            //    var filter = new RelationPredicateBucket(StoreShelfBalanceFields.StoreId == storeId);
            //    filter.PredicateExpression.Add(StoreShelfBalanceFields.ItemId == itemId);

            //    DALHelper.FetchCollection(itemBalances, filter);

            //    foreach (var item in itemBalances)
            //    {
            //        initialQuantity += item.Balance;
            //        totlaValue += (item.Balance * item.UnitPrice.Value);
            //    }
            //}

            if (initialQuantity <= 0)
                return;

            initialPrice = totlaValue / initialQuantity;
        }

        public static ExamineDoc GetExamineBySerial(string serial)
        {
            var context = new ExtendedDataContext();
            return context.ExamineDocs.Where(x => x.Serial == serial).FirstOrDefault();
        }

        public static DataTable GetDetailedOutput(int projectId, int contractorId, int? itemId, int storeId, DateTime startDate, DateTime endDate)
        {
            var dt = new DataTable();
            var fields = new ResultsetFields(6);
            fields.DefineField(TransactionFields.Date, 0);
            fields.DefineField(TransactionItemFields.Quantity, 1);
            fields.DefineField(TransactionItemFields.UnitPrice, 2);
            fields.DefineField(TransactionItemFields.Id.SetExpression(new Expression(fields[1], ExOp.Mul, fields[2])), 3, "TotalPrice");
            fields.DefineField(ItemFields.Name, 4);
            fields.DefineField(TransactionFields.PermitNumber, 5);

            var filter = new RelationPredicateBucket(TransactionItemFields.StoreId == storeId);

            if (itemId != null)
                filter.PredicateExpression.Add(TransactionItemFields.ItemId == itemId.Value);

            filter.PredicateExpression.Add(TransactionFields.ProjectId == projectId);
            filter.PredicateExpression.Add(WithdrawTransactionFields.ContractorId == contractorId);
            filter.PredicateExpression.Add(TransactionFields.Date >= startDate);
            filter.PredicateExpression.Add(TransactionFields.Date <= endDate);

            filter.Relations.Add(TransactionEntity.Relations.TransactionItemEntityUsingTransactionId);
            filter.Relations.Add(TransactionEntity.Relations.WithdrawTransactionEntityUsingTransactionId);
            filter.Relations.Add(TransactionItemEntity.Relations.ItemEntityUsingItemId);

            var sorter = new SortExpression((EntityField2)fields[0] | SortOperator.Ascending);

            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(fields, dt, filter, 0, sorter, false);
            }

            return dt;
        }
        public static DataTable GetDetailedSupply(int projectId, int supplierId, int? itemId, int storeId, DateTime startDate, DateTime endDate)
        {
            var dt = new DataTable();
            var fields = new ResultsetFields(6);
            fields.DefineField(TransactionFields.Date, 0);
            fields.DefineField(TransactionItemFields.Quantity, 1);
            fields.DefineField(TransactionItemFields.UnitPrice, 2);
            fields.DefineField(TransactionItemFields.Id.SetExpression(new Expression(fields[1], ExOp.Mul, fields[2])), 3, "TotalPrice");
            fields.DefineField(ItemFields.Name, 4);
            fields.DefineField(TransactionFields.PermitNumber, 5);

            var filter = new RelationPredicateBucket(TransactionItemFields.StoreId == storeId);

            if (itemId != null)
                filter.PredicateExpression.Add(TransactionItemFields.ItemId == itemId.Value);

            filter.PredicateExpression.Add(TransactionFields.ProjectId == projectId);
            filter.PredicateExpression.Add(DepositTransactionFields.SupplierId == supplierId);
            filter.PredicateExpression.Add(TransactionFields.Date >= startDate);
            filter.PredicateExpression.Add(TransactionFields.Date <= endDate);

            filter.Relations.Add(TransactionEntity.Relations.TransactionItemEntityUsingTransactionId);
            filter.Relations.Add(TransactionEntity.Relations.DepositTransactionEntityUsingTransactionId);
            filter.Relations.Add(TransactionItemEntity.Relations.ItemEntityUsingItemId);

            var sorter = new SortExpression((EntityField2)fields[0] | SortOperator.Ascending);

            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(fields, dt, filter, 0, sorter, false);
            }

            return dt;
        }

        public static List<TransactionItemBrief> GetDetailedSupplyFromTo(string fromStoreCode, string toStoreCode, string fromProdCode, string toProdCode,
            string fromSupplierCode, string toSupplierCode, DateTime startDate, DateTime endDate)
        {
            long fromStoreCodeNo;
            long toStoreCodeNo;
            long fromProdCodeNo;
            long toProdCodeNo;
            long fromSupplierCodeNo;
            long toSupplierCodeNo;

            if (long.TryParse(fromStoreCode, out fromStoreCodeNo) &&
            long.TryParse(toStoreCode, out toStoreCodeNo) &&
            long.TryParse(fromProdCode, out fromProdCodeNo) &&
            long.TryParse(toProdCode, out toProdCodeNo) &&
            long.TryParse(fromSupplierCode, out fromSupplierCodeNo) &&
            long.TryParse(toSupplierCode, out toSupplierCodeNo))
            {

                var metaData = new LinqMetaData();

                var query = from trans in metaData.Transaction
                            from transItem in trans.TransactionItems
                            from depositTrans in trans.DepositTransactions
                            where (trans.Date >= startDate && trans.Date < endDate &&
                                   Convert.ToInt64(transItem.Store.Code) >= fromStoreCodeNo &&
                                   Convert.ToInt64(transItem.Store.Code) < toStoreCodeNo &&
                                   Convert.ToInt64(transItem.Item.Code) >= fromProdCodeNo &&
                                   Convert.ToInt64(transItem.Item.Code) < toProdCodeNo &&
                                   Convert.ToInt64(depositTrans.Supplier.Code) >= fromSupplierCodeNo &&
                                   Convert.ToInt64(depositTrans.Supplier.Code) < toSupplierCodeNo)
                            orderby trans.Date ascending
                            select new TransactionItemBrief
                            {
                                Date = trans.Date,
                                Name = transItem.Item.Name,
                                PermitNumber = trans.PermitNumber,
                                Quantity = transItem.Quantity,
                                UnitPrice = transItem.UnitPrice,
                                TotalPrice = transItem.UnitPrice * transItem.Quantity
                            };

                using (var adapter = new DataAccessAdapter())
                {
                    ((LLBLGenProProvider2)(query).Provider).AdapterToUse = adapter;
                    return query.ToList();
                }

                //var dt = new DataTable();
                //var fields = new ResultsetFields(6);
                //fields.DefineField(TransactionFields.Date, 0);
                //fields.DefineField(TransactionItemFields.Quantity, 1);
                //fields.DefineField(TransactionItemFields.UnitPrice, 2);
                //fields.DefineField(
                //    TransactionItemFields.Id.SetExpression(new Expression(fields[1], ExOp.Mul, fields[2])), 3,
                //    "TotalPrice");
                //fields.DefineField(ItemFields.Name, 4);
                //fields.DefineField(TransactionFields.PermitNumber, 5);


                //var filter = new RelationPredicateBucket();
                //filter.Relations.Add(TransactionItemEntity.Relations.StoreEntityUsingStoreId);
                //filter.Relations.Add(TransactionEntity.Relations.TransactionItemEntityUsingTransactionId);
                //filter.Relations.Add(TransactionEntity.Relations.DepositTransactionEntityUsingTransactionId);
                //filter.Relations.Add(TransactionItemEntity.Relations.ItemEntityUsingItemId);
                //filter.Relations.Add(DepositTransactionEntity.Relations.SupplierEntityUsingSupplierId);

                //filter.PredicateExpression.Add(TransactionFields.Date >= startDate);
                //filter.PredicateExpression.Add(TransactionFields.Date <= endDate);

                //for (long i = fromStoreCodeNo; i <= toStoreCodeNo; i++)
                //{
                //    if (i == fromStoreCodeNo)
                //    {
                //        filter.PredicateExpression.Add(StoreFields.Code == i.ToString());
                //    }
                //    else
                //    {
                //        filter.PredicateExpression.AddWithOr(StoreFields.Code == i.ToString());
                //    }
                //}

                //for (long i = fromProdCodeNo; i <= toProdCodeNo; i++)
                //{
                //    if(i==fromProdCodeNo)
                //    {
                //        filter.PredicateExpression.Add(ItemFields.Code == i.ToString());  
                //    }
                //    else
                //    {
                //        filter.PredicateExpression.AddWithOr(ItemFields.Code == i.ToString());                               
                //    }
                //}

                //for (long i = fromSupplierCodeNo; i <= toSupplierCodeNo; i++)
                //{
                //    if(i==fromSupplierCodeNo)
                //    {
                //        filter.PredicateExpression.Add(SupplierFields.Code == i.ToString());
                //    }
                //    else
                //    {
                //        filter.PredicateExpression.AddWithOr(SupplierFields.Code == i.ToString());    
                //    }

                //}

                //var sorter = new SortExpression((EntityField2) fields[0] | SortOperator.Ascending);

                ////var metaData = new LinqMetaData();
                ////var q = from c in metaData.DepositTransaction select c;



                //using (var adapter = new DataAccessAdapter())
                //{
                //    adapter.FetchTypedList(fields, dt, filter, 0, sorter, false);
                //}

                //return dt;
            }

            return null;

        }


        public static List<TransactionItemBrief> GetSupplierAnalysisReport(string supplierRange, string storeRange, string productRange,
            DateTime startDate, DateTime endDate)
        {
            List<string> suppliers = supplierRange.Split(new string[1] {","}, StringSplitOptions.RemoveEmptyEntries).ToList();
            List<string> stores = storeRange.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
            //List<string> products = productRange.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();

            var metaData = new LinqMetaData();

            var query = from trans in metaData.Transaction
                        from transItem in trans.TransactionItems
                        from depositTrans in trans.DepositTransactions
                        where (trans.Date >= startDate && trans.Date < endDate &&
                               trans.Type == "Deposit" && 
                               productRange.IndexOf(transItem.Item.Code) != -1 &&//products.Contains(transItem.Item.Code) &&
                               stores.Contains(transItem.Store.Code) &&
                               suppliers.Contains(depositTrans.Supplier.Code))
                        orderby trans.Date ascending
                        select new TransactionItemBrief
                                   {
                                       Date = trans.Date,
                                       Name = transItem.Item.Name,
                                       PermitNumber = trans.PermitNumber,
                                       Quantity = transItem.Quantity,
                                       UnitPrice = transItem.UnitPrice,
                                       TotalPrice = transItem.UnitPrice*transItem.Quantity,
                                       ProductCode = transItem.Item.Code,
                                       StoreCode = transItem.Store.Code,
                                       StoreName = transItem.Store.Name,
                                       SupplierName = depositTrans.Supplier.Name
                                   };

            using (var adapter = new DataAccessAdapter())
            {
                ((LLBLGenProProvider2)(query).Provider).AdapterToUse = adapter;
                return query.ToList();
            }
        }

        public static List<TransactionItemBrief> GetIssuedAnalysisReport(string contractorRange, string storeRange, string productRange,
            DateTime startDate, DateTime endDate)
        {
            List<string> contractors = contractorRange.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
            List<string> stores = storeRange.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
            //List<string> products = productRange.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();

            var metaData = new LinqMetaData();

            var query = from trans in metaData.Transaction
                        from transItem in trans.TransactionItems
                        from withdrawalTrans in trans.WithdrawTransactions
                        where (trans.Date >= startDate && trans.Date < endDate &&
                               trans.Type=="Withdraw" &&
                               productRange.IndexOf(transItem.Item.Code) != -1 &&//products.Contains(transItem.Item.Code) &&
                               stores.Contains(transItem.Store.Code) &&
                               contractors.Contains(withdrawalTrans.Contractor.Code))
                        orderby trans.Date ascending
                        select new TransactionItemBrief
                        {
                            Date = trans.Date,
                            Name = transItem.Item.Name,
                            PermitNumber = trans.PermitNumber,
                            Quantity = transItem.Quantity,
                            UnitPrice = transItem.UnitPrice,
                            TotalPrice = transItem.UnitPrice * transItem.Quantity,
                            ProductCode = transItem.Item.Code,
                            StoreCode = transItem.Store.Code,
                            StoreName = transItem.Store.Name
                        };

            using (var adapter = new DataAccessAdapter())
            {
                ((LLBLGenProProvider2)(query).Provider).AdapterToUse = adapter;
                return query.ToList();
            }
        }

        public static List<TransactionItemBrief> GetTransferAnalysisReport(string fromStoreRange, string toStoreRange, string productRange,
            DateTime startDate, DateTime endDate)
        {
            List<string> fromStores = fromStoreRange.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
            List<string> toStores = toStoreRange.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
            //List<string> products = productRange.Split(new string[1] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();

            var metaData = new LinqMetaData();

            var query = from trans in metaData.Transaction
                        from transItem in trans.TransactionItems
                        from transferTrans in trans.TransferTransactions
                        join fromStore in metaData.Store on transferTrans.FromStoreId equals fromStore.Id
                        join toStore in metaData.Store on transferTrans.ToStoreId equals toStore.Id

                        where (trans.Date >= startDate && trans.Date < endDate &&       
                               productRange.IndexOf(transItem.Item.Code) != -1 &&//products.Contains(transItem.Item.Code) &&
                               fromStores.Contains(fromStore.Code) &&
                               toStores.Contains(toStore.Code))
                        orderby trans.Date ascending
                        select new TransactionItemBrief
                        {
                            Date = trans.Date,
                            Name = transItem.Item.Name,
                            PermitNumber = trans.PermitNumber,
                            Quantity = transItem.Quantity,
                            UnitPrice = transItem.UnitPrice,
                            TotalPrice = transItem.UnitPrice * transItem.Quantity,
                            ProductCode = transItem.Item.Code,
                            StoreCode = transItem.Store.Code,
                            StoreName = transItem.Store.Name
                        };

            using (var adapter = new DataAccessAdapter())
            {
                ((LLBLGenProProvider2)(query).Provider).AdapterToUse = adapter;
                return query.ToList();
            }
        }

        public static List<TransactionItemBrief> GetDetailedIssuedFromTo(string fromStoreCode, string toStoreCode, string fromProdCode, string toProdCode,
           string fromContractorCode, string toContractorCode, DateTime startDate, DateTime endDate)
        {
            long fromStoreCodeNo;
            long toStoreCodeNo;
            long fromProdCodeNo;
            long toProdCodeNo;
            long fromContractorCodeNo;
            long toContarctorCodeNo;

            if (long.TryParse(fromStoreCode, out fromStoreCodeNo) &&
            long.TryParse(toStoreCode, out toStoreCodeNo) &&
            long.TryParse(fromProdCode, out fromProdCodeNo) &&
            long.TryParse(toProdCode, out toProdCodeNo) &&
            long.TryParse(fromContractorCode, out fromContractorCodeNo) &&
            long.TryParse(toContractorCode, out toContarctorCodeNo))
            {
                //List<string> storeRange = new List<string>();
                //List<string> prodRange = new List<string>();
                //List<string> contRange = new List<string>();

                //for (long i = fromStoreCodeNo; i <= toStoreCodeNo; i++)
                //{
                //    storeRange.Add(i.ToString());
                //}
                //for (long i = fromProdCodeNo; i <= toProdCodeNo; i++)
                //{
                //    prodRange.Add(i.ToString());
                //}

                //for (long i = fromContractorCodeNo; i <= toContarctorCodeNo; i++)
                //{
                //    contRange.Add(i.ToString());
                //}

                var metaData = new LinqMetaData();

                var query = from trans in metaData.Transaction
                            from transItem in trans.TransactionItems
                            from withDrawTrans in trans.WithdrawTransactions
                            where (trans.Date >= startDate && trans.Date < endDate &&
                                   Convert.ToInt64(transItem.Store.Code) >= fromStoreCodeNo &&
                                   Convert.ToInt64(transItem.Store.Code) < toStoreCodeNo &&
                                   Convert.ToInt64(transItem.Item.Code) >= fromProdCodeNo &&
                                   Convert.ToInt64(transItem.Item.Code) < toProdCodeNo &&
                                   Convert.ToInt64(withDrawTrans.Contractor.Code) >= fromContractorCodeNo &&
                                   Convert.ToInt64(withDrawTrans.Contractor.Code) < toContarctorCodeNo)
                            //storeRange.Contains(transItem.Store.Code) &&
                            //prodRange.Contains(transItem.Item.Code) &&
                            //contRange.Contains(withDrawTrans.Contractor.Code) && 
                            //Convert.ToInt64(withDrawTrans.Contractor.Code) > 5000)
                            //let qty = transItem.Quantity
                            //let unitPrice = transItem.UnitPrice
                            orderby trans.Date ascending
                            select new TransactionItemBrief
                                       {
                                           Date = trans.Date,
                                           Name = transItem.Item.Name,
                                           PermitNumber = trans.PermitNumber,
                                           Quantity = transItem.Quantity,
                                           UnitPrice = transItem.UnitPrice,
                                           TotalPrice = transItem.UnitPrice * transItem.Quantity
                                       };

                //var dt = new DataTable();
                //var fields = new ResultsetFields(6);
                //fields.DefineField(TransactionFields.Date, 0);
                //fields.DefineField(TransactionItemFields.Quantity, 1);
                //fields.DefineField(TransactionItemFields.UnitPrice, 2);
                //fields.DefineField(
                //    TransactionItemFields.Id.SetExpression(new Expression(fields[1], ExOp.Mul, fields[2])), 3,
                //    "TotalPrice");
                //fields.DefineField(ItemFields.Name, 4);
                //fields.DefineField(TransactionFields.PermitNumber, 5);


                //var filter = new RelationPredicateBucket();

                //filter.Relations.Add(TransactionItemEntity.Relations.StoreEntityUsingStoreId);
                //filter.Relations.Add(TransactionEntity.Relations.TransactionItemEntityUsingTransactionId);
                //filter.Relations.Add(TransactionEntity.Relations.WithdrawTransactionEntityUsingTransactionId);
                //filter.Relations.Add(TransactionItemEntity.Relations.ItemEntityUsingItemId);
                //filter.Relations.Add(WithdrawTransactionEntity.Relations.ContractorEntityUsingContractorId);

                //filter.PredicateExpression.Add(TransactionFields.Date >= startDate);
                //filter.PredicateExpression.Add(TransactionFields.Date <= endDate);

                //for (long i = fromStoreCodeNo; i <= toStoreCodeNo; i++)
                //{
                //    if (i == fromStoreCodeNo)
                //    {
                //        filter.PredicateExpression.Add(StoreFields.Code == i.ToString());
                //    }
                //    else
                //    {
                //        filter.PredicateExpression.AddWithOr(StoreFields.Code == i.ToString());
                //    }
                //}

                //for (long i = fromProdCodeNo; i <= toProdCodeNo; i++)
                //{
                //    if (i == fromProdCodeNo)
                //    {
                //        filter.PredicateExpression.Add(ItemFields.Code == i.ToString());
                //    }
                //    else
                //    {
                //        filter.PredicateExpression.AddWithOr(ItemFields.Code == i.ToString());
                //    }
                //}

                //for (long i = fromContractorCodeNo; i <= toContarctorCodeNo; i++)
                //{
                //    if (i == fromContractorCodeNo)
                //    {
                //        filter.PredicateExpression.Add(ContractorFields.Code == i.ToString());
                //    }
                //    else
                //    {
                //        filter.PredicateExpression.AddWithOr(ContractorFields.Code == i.ToString());
                //    }

                //}

                //var sorter = new SortExpression((EntityField2)fields[0] | SortOperator.Ascending);

                using (var adapter = new DataAccessAdapter())
                {
                    ((LLBLGenProProvider2)((IQueryable)query).Provider).AdapterToUse = adapter;
                    return query.ToList();
                }
            }

            return null;
        }

        /// <summary>
        /// Convert IList to data table
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        public static DataTable ConvertListToDataTable<T>(IList<T> data)// T is any generic type
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            }
            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }

    }


    public class ItemCard
    {
        public DateTime Date { get; set; }
        public decimal InQuantity { get; set; }
        public decimal InPrice { get; set; }
        public decimal InTotal { get; set; }
        public decimal OutQuantity { get; set; }
        public decimal OutPrice { get; set; }
        public decimal OutTotal { get; set; }
        public decimal AccumulatedQuantity { get; set; }
        public decimal AccumulatedTotal { get; set; }
        public string AccountingNumber { get; set; }
        public string PermitNumber { get; set; }
        public string Comment { get; set; }
        public DateTime AccountingTransDate { get; set; }
        public long TransactionId { get; set; }
        public decimal AllInQuantity { get; set; }
        public decimal AllInPrice { get; set; }
        public decimal AllOutQuantity { get; set; }
        public decimal AllOutPrice { get; set; }
        public string TransType { get; set; }

    }

    public class SupplyOrderItem
    {

        public int Sequence { get; set; }

        public decimal Quantity { get; set; }
        public decimal UnitPrice { get; set; }
        public decimal Total { get; set; }
        public string Unit { get; set; }

        public string Comment { get; set; }
    }

}
